package com.qf.index;

import cn.hutool.core.collection.ConcurrentHashSet;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;

import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

public class C3 {

    public static void main(String[] args) throws SQLException {
        // create table basic_core_paper_1000w select * from basic_core_paper limit 10000000;
        String newDataName = "scholar_25_05.";
        String summaryTable = newDataName + "unified_search_summary_0406_temp";
        String summaryFinalTable = newDataName + "unified_search_summary_0406";
        String summaryTypeTable = newDataName + "unified_search_summary_0406_type";
        String fullAbbrKeyWordTable = newDataName + "full_abbr_key_word_0406";
        String keyWordSumTable = newDataName + "key_word_sum_0406";
        String spiderCnCoreTable = newDataName + "spider_cn_core";
        String spiderCnKiAnalysisTable = newDataName + "spider_cnki_analysis";
        String spiderCqVipAnalysisTable = newDataName + "spider_cqvip_analysis";
        String spiderWanFangAnalysisTable = newDataName + "spider_wanfang_analysis";
        String spiderYiiGleAnalysisTable = newDataName + "spider_yiigle_analysis";
        String pmArticleTable = newDataName + "spider_pubmed_vip_extend";
        String utArticleTable = newDataName + "spider_tb_true_92007022_1125";
        String jourMergeTable = newDataName + "basic_jour_merge";
        String quotaTable = newDataName + "basic_core_paper_quota";
        String basicCoreTable = newDataName + "basic_core_paper_1000w";
        String wordAndCodeSetMapTable = newDataName + "word_and_code_set_map";
        String meshTable = newDataName + "help_mesh_tree_gcr_0615";
        String htLocationChinaTable = newDataName + "ht_location_china";
        String basicHospUnivDeptTable = newDataName + "basic_hosp_or_univ_dept";
        String hospUnivInfoSumTable = newDataName + "hosp_or_univ_info_sum";
        String citationTable = newDataName + "basic_core_paper_citation";
        String referenceTable = newDataName + "basic_core_paper_reference_id";
        String doiTable = newDataName + "doi_location_20230112";
        String doiAddTable = newDataName + "doi_location_20230112_add";
        String ncTextTable = "html.nc_text_table";
        String standardUnitSumTable = newDataName + "basic_standard_unit_sum";
        String pmDataAnalysisTable = newDataName + "pm_data_analysis_fund_unit";
        String titleTransTable = newDataName + "basic_title_trans";
        String bibPmTable = newDataName + "bibliometric_pm_0406";
        String bibUtTable = newDataName + "bibliometric_ut_0406";
        String bibAidTable = newDataName + "bibliometric_aid_0406";
        String ncTextTopTable = newDataName + "nc_text_table_top_0406";
        String abstractTransTable = newDataName + "basic_abstract_trans";
        Map<String, Integer> termAndSumMap = new ConcurrentHashMap<>();

        createData(basicCoreTable, spiderCnCoreTable, spiderCnKiAnalysisTable, spiderCqVipAnalysisTable, spiderWanFangAnalysisTable, spiderYiiGleAnalysisTable, pmArticleTable, utArticleTable);


    }


    public static void createData(String basicCoreTable, String spiderCnCoreTable, String spiderCnKiAnalysisTable, String spiderCqVipAnalysisTable, String spiderWanFangAnalysisTable, String spiderYiiGleAnalysisTable, String pmArticleTable, String utArticleTable) throws SQLException {
        Set<Integer> zwIdSet = new ConcurrentHashSet<>();
        Set<Integer> wfIdSet = new ConcurrentHashSet<>();
        Set<Integer> wpIdSet = new ConcurrentHashSet<>();
        Set<Integer> zhIdSet = new ConcurrentHashSet<>();
        Set<String> pmIdSet = new ConcurrentHashSet<>();
        Set<String> csIdSet = new ConcurrentHashSet<>();
        Set<String> utIdSet = new ConcurrentHashSet<>();
        String field = "cnki_id`,`wanfang_id`,`weipu_id`,`pm`,`cscd_ut`,`wos_ut`,`yiigle_id";
        getIdSet(basicCoreTable, "id", field, zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "getId", new HashSet<>(), new HashSet<>());
        System.out.println("zwIdSet.size(): " + zwIdSet.size());
        System.out.println("wfIdSet.size(): " + wfIdSet.size());
        System.out.println("wpIdSet.size(): " + wpIdSet.size());
        System.out.println("zhIdSet.size(): " + zhIdSet.size());
        System.out.println("pmIdSet.size(): " + pmIdSet.size());
        System.out.println("csIdSet.size(): " + csIdSet.size());
        System.out.println("utIdSet.size(): " + utIdSet.size());
        //zwIdSet.size(): 810024
        //wfIdSet.size(): 1216121
        //wpIdSet.size(): 1692727
        //zhIdSet.size(): 107818
        //pmIdSet.size(): 3650147
        //csIdSet.size(): 485275
        //utIdSet.size(): 6509904

//        getIdSet(spiderCnCoreTable, "id", "UT", zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "deleteUt", new HashSet<>(), csIdSet);
//        getIdSet(utArticleTable, "pid", "UT", zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "deleteUt", new HashSet<>(), utIdSet);
        getIdSet(pmArticleTable, "id", "pmid", zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "deleteUt", new HashSet<>(), pmIdSet);

//        getIdSet(spiderCnKiAnalysisTable, "id", "", zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "deleteId", zwIdSet, new HashSet<>());
//        getIdSet(spiderCqVipAnalysisTable, "id", "", zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "deleteId", wpIdSet, new HashSet<>());
//        getIdSet(spiderWanFangAnalysisTable, "id", "", zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "deleteId", wfIdSet, new HashSet<>());
//        getIdSet(spiderYiiGleAnalysisTable, "pid", "", zwIdSet, wfIdSet, wpIdSet, zhIdSet, pmIdSet, csIdSet, utIdSet, "deleteId", zhIdSet, new HashSet<>());


    }

    public static void getIdSet(String saveTable, String autoId, String field, Set<Integer> zwIdSet, Set<Integer> wfIdSet, Set<Integer> wpIdSet, Set<Integer> zhIdSet, Set<String> pmIdSet, Set<String> csIdSet, Set<String> utIdSet,
                                String type, Set<Integer> idSet, Set<String> utSet) throws SQLException {
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + saveTable);
        String limit = "";
//        String limit = " limit 1";
        int onePageNum = 1000;
        if (tableNum.getFirst().getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + saveTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            tableList.parallelStream().forEach(startId -> {
                try {
                    List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + saveTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
                    for (Entity entry : tableData) {
                        if (type.equals("getId")) {
                            getAutoId(Utils.getStrField(entry, "cnki_id"), zwIdSet);
                            getAutoId(Utils.getStrField(entry, "wanfang_id"), wfIdSet);
                            getAutoId(Utils.getStrField(entry, "weipu_id"), wpIdSet);
                            getAutoId(Utils.getStrField(entry, "yiigle_id"), zhIdSet);
                            getUt(Utils.getStrField(entry, "pm"), pmIdSet);
                            getUt(Utils.getStrField(entry, "cscd_ut"), csIdSet);
                            getUt(Utils.getStrField(entry, "wos_ut"), utIdSet);
                        }
                        if (type.equals("deleteId")) {
                            int id = Utils.getIntField(entry, autoId);
                            if (!idSet.contains(id)) {
                                String sql = "delete from " + saveTable + " where " + autoId + " = " + id + ";";
                                Db.use().execute(sql);
                            }
                        }
                        if (type.equals("deleteUt")) {
                            String ut = Utils.getStrField(entry, field).toUpperCase().replace(" ", "").replace("CSCD:", "").replace("WOS:", "").trim();
                            if (!utSet.contains(ut)) {
                                int id = Utils.getIntField(entry, autoId);
                                String sql = "delete from " + saveTable + " where " + autoId + " = " + id + ";";
                                Db.use().execute(sql);
                            }
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
        }
    }

    public static void getUt(String ids, Set<String> idSet) {
        if (null != ids) {
            String[] split = ids.replace(" ", "").split(",");
            for (String id : split) {
                if (null != id && !id.trim().isEmpty()) {
                    idSet.add(id.trim());
                }
            }
        }
    }

    // 因鲍工的程序，各表中的自增主键也不是一个，而是一个逗号分割的多个，所以在这里只取其中的一个即可。
    public static void getAutoId(String ids, Set<Integer> idSet) {
        if (null != ids) {
            String[] split = ids.replace(" ", "").split(",");
            for (String id : split) {
                if (NumberUtil.isInteger(id.trim())) {
                    int i = Integer.parseInt(id.trim());
                    if (i > 0) {
                        idSet.add(i);
                    }
                }
            }
        }
    }

}
